Toolips Tools Shop Database

THE PROJECT

Overview

Our team was given raw Excel files for a tool shop's customers, orders, tools, suppliers, warehouses, and charity partners, and asked to turn them into a working relational database.

Over three deliverables we wrote business rules, modeled the data in an EER diagram, converted it to a relational schema, normalized it, and implemented and queried it in Oracle APEX.

KEY TAKEAWAYS

What I Learned

Start with business rules, not tables

A database is only as good as its understanding of the business. Writing rules like "each order is placed by exactly one customer" first made every later design choice (cardinality, keys, optional vs. required fields) easy to justify.

APPLIEDWe wrote 18 numbered business rules and labeled each relationship on the diagram with the rule behind it.

Modeling complex relationships in an EER diagram

I learned to resolve many-to-many relationships with associative entities, model a ternary relationship when a fact only exists with three parties together, and use a supertype/subtype when records share most attributes but differ in a few.

APPLIEDOrderLine links orders and tools; SupplySchedule records which supplier delivered which tool to which warehouse; Tool splits into disjoint New (warranty) and Used (condition) subtypes.

Final EER diagram of the Toolips Tools Shop database — Our final EER diagram. Click to open full size.

Primary keys, foreign keys, and data integrity

Choosing the right keys keeps data trustworthy. I learned when to use a natural key, a composite key, or a surrogate key, and how foreign keys stop "orphan" records.

APPLIEDWe added a surrogate key to SupplySchedule so the same supplier–tool–warehouse combination could repeat on different dates, and verified every foreign key pointed to an existing record.

Normalization (1NF → 2NF → 3NF)

Real data is messy. Normalization removes duplicate data so that each fact is stored once and updates don't create inconsistencies.

APPLIEDThe charity spreadsheet packed several phone numbers into one cell and repeated manager and neighborhood details on every row. We split it into six tables: Charity, Managers, ManagerPhoneNumbers, Neighborhood, CharityMembers, and CharityAssociative.

Original charity spreadsheet before normalization — Before: the original charity sheet.

Normalized charity tables — After: normalized into related tables.

Building and querying a database with SQL

I turned the design into real tables in Oracle APEX using CREATE TABLE with data types, NOT NULL constraints, and primary keys, then wrote queries with joins, GROUP BY, HAVING, and nested subqueries to answer business questions.

APPLIEDExample: finding the customer who placed the most orders.

SELECT c.CustomerID, c.CustomerName
FROM Customer c
JOIN ORDERTABLE o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING COUNT(o.OrderNumber) = (
    SELECT MAX(OrderCount) FROM (
        SELECT COUNT(OrderNumber) AS OrderCount
        FROM ORDERTABLE GROUP BY CustomerID));

View result in APEX ↗

Working as a team with a structured process

With fixed, sequential deadlines, a structured SDLC approach kept us on track. Reviewing each other's diagrams in working sessions caught errors that one person would have missed.